package com.sl.au.repository;

import com.sl.au.entity.SinglePump;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

@Transactional(readOnly = true)
public interface SinglePumpRepository extends JpaRepository<SinglePump, String> {
	/*
	 *  得到主机的价格
	 */
	@Query(" select s from SinglePump s where s.superior =?1 and "
			+ "(s.device.deviceModel like '%'+?2+'%' "
			+ "or s.device.deviceType like '%'+?2+'%' "
			+ "or s.device.deviceName like '%'+?2+'%')")	
	Page<SinglePump> getPumpPrice(String superior,String key, Pageable pageable);
	
	
	/**
	 * 得到数据条数
	 */
	@Query(" select count(s) from SinglePump s where s.superior =?1 and "
			+ "(s.device.deviceModel like '%'+?2+'%' "
			+ "or s.device.deviceType like '%'+?2+'%' "
			+ "or s.device.deviceName like '%'+?2+'%')")	
	long getCount(String superior,String key);
	
	@Query(" select s from SinglePump s where "
			+ "s.device.deviceModel like '%'+?1+'%' "
			+ "or s.device.deviceType like '%'+?1+'%' "
			+ "or s.device.deviceName like '%'+?1+'%'")	
	Page<SinglePump> getPumpPrice(String key, Pageable pageable);
	
	@Query(" select count(s) from SinglePump s where "
			+ "s.device.deviceModel like '%'+?1+'%' "
			+ "or s.device.deviceType like '%'+?1+'%' "
			+ "or s.device.deviceName like '%'+?1+'%'")	
	long getCount(String key);
	
	/*
	 * 更新主机价格的设备型号
	 */
	@Transactional
	@Modifying  
	@Query("update SinglePump s set s.type=?1 where s.device.id=?2 ")
	public void updateTypeByDeviceId(String type,String deviceId);
	

	/*
	 * 删除与主机ID相关的价格
	 */
	@Transactional
	@Modifying  
	@Query("delete from SinglePump s where s.device.id=?1 ")
	public void deleteDevicePrice(String deviceId);
	
	/*
	 * 通过部门和类型找到价格
	 */
	@Query("select s from SinglePump s where s.type=?1 and s.superior=?2")
	SinglePump findByTypeAndSuperior(String type,String superior);

}
